import sqlite3
import json
import csv

class Question:
    def __init__(self, course_id, knowledge_point_id, question_type, content, options, answer, explanation, difficulty):
        self.course_id = course_id
        self.knowledge_point_id = knowledge_point_id
        self.question_type = question_type
        self.content = content
        self.options = options
        self.answer = answer
        self.explanation = explanation
        self.difficulty = difficulty

class QuestionBank:
    def __init__(self, db_path='question_bank.db'):
        self.db_path = db_path

    def add_question(self, question):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        cursor.execute('''
        INSERT INTO questions (course_id, knowledge_point_id, question_type, content, options, answer, explanation, difficulty)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (question.course_id, question.knowledge_point_id, question.question_type, question.content, json.dumps(question.options), question.answer, question.explanation, question.difficulty))
        conn.commit()
        conn.close()

    def delete_question(self, question_id):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        cursor.execute('DELETE FROM questions WHERE id = ?', (question_id,))
        conn.commit()
        conn.close()

    def update_question(self, question_id, question):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        cursor.execute('''
        UPDATE questions
        SET course_id = ?, knowledge_point_id = ?, question_type = ?, content = ?, options = ?, answer = ?, explanation = ?, difficulty = ?
        WHERE id = ?
        ''', (question.course_id, question.knowledge_point_id, question.question_type, question.content, json.dumps(question.options), question.answer, question.explanation, question.difficulty, question_id))
        conn.commit()
        conn.close()

    def get_all_questions(self):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM questions')
        rows = cursor.fetchall()
        conn.close()
        return [self.row_to_question(row) for row in rows]

    def row_to_question(self, row):
        return {
            'id': row[0],
            'course_id': row[1],
            'knowledge_point_id': row[2],
            'question_type': row[3],
            'content': row[4],
            'options': json.loads(row[5]) if row[5] else None,
            'answer': row[6],
            'explanation': row[7],
            'difficulty': row[8]
        }

    def import_questions_from_csv(self, filepath):
        with open(filepath, mode='r', encoding='utf-8') as file:
            reader = csv.DictReader(file)
            for row in reader:
                question = Question(
                    course_id=int(row['course_id']),
                    knowledge_point_id=int(row['knowledge_point_id']),
                    question_type=row['question_type'],
                    content=row['content'],
                    options=row['options'].split('|') if row['options'] else None,
                    answer=row['answer'],
                    explanation=row['explanation'],
                    difficulty=float(row['difficulty'])
                )
                self.add_question(question)

    def export_questions_to_csv(self, filepath):
        questions = self.get_all_questions()
        with open(filepath, mode='w', encoding='utf-8') as file:
            writer = csv.DictWriter(file, fieldnames=['id', 'course_id', 'knowledge_point_id', 'question_type', 'content', 'options', 'answer', 'explanation', 'difficulty'])
            writer.writeheader()
            for question in questions:
                writer.writerow({
                    'id': question['id'],
                    'course_id': question['course_id'],
                    'knowledge_point_id': question['knowledge_point_id'],
                    'question_type': question['question_type'],
                    'content': question['content'],
                    'options': '|'.join(question['options']) if question['options'] else '',
                    'answer': question['answer'],
                    'explanation': question['explanation'],
                    'difficulty': question['difficulty']
                })

    def analyze_questions(self):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        cursor.execute('SELECT course_id, COUNT(*) FROM questions GROUP BY course_id')
        course_counts = cursor.fetchall()
        cursor.execute('SELECT knowledge_point_id, COUNT(*) FROM questions GROUP BY knowledge_point_id')
        knowledge_point_counts = cursor.fetchall()
        cursor.execute('SELECT question_type, COUNT(*) FROM questions GROUP BY question_type')
        question_type_counts = cursor.fetchall()
        conn.close()

        analysis = {
            'course_counts': course_counts,
            'knowledge_point_counts': knowledge_point_counts,
            'question_type_counts': question_type_counts
        }
        return analysis
